(*
	Purpose: Demo to query/retrieve/format data from a simple Sqlite3 db/table
	Author: Michael 
	Date: 15-Feb-2007
*)

set head to "sqlite3  -column ~/desktop/TestDB.db" & space & quote
set tail to quote

(*
--demo to select a people's fullname by his/her country
set sql to "select firstname, lastname from Demo where country = 'Canada';"
set {firstname, lastname} to words of (do shell script head & sql & tail)
*)



-- demo to retrieve data and format it to 
--   LastName, FirstName   e.g.. Liu, Michael
set sql to "select firstname, lastname from Demo;"

set N to words of (do shell script head & sql & tail)
set tNames to {}
repeat with i from 1 to (count N) - 1 by 2
	tell N to set end of tNames to item (i + 1) & ", " & space & item i
end repeat
tNames


-- demo to select a people's name from the popup list, then retrieve its country
set who to (choose from list tNames with prompt "Select a name to get the country" with title "Choose a people") as text

set tFirstName to the second word of who
set tLastName to the first word of who

set sql to "select country from Demo where firstname = '" & tFirstName & "' and lastname = '" & tLastName & "'"

set country to (do shell script head & sql & tail)
country


